---------------------------------------------
-- Export file for user DEMO01             --
-- Created by user on 28/06/2012, 11:04:44 --
---------------------------------------------

spool scritBDDemo01.log

prompt
prompt Creating table APACHELOG4JORACLE
prompt ================================
prompt
create table DEMO01.APACHELOG4JORACLE
(
  ID        NUMBER(5) not null,
  FECHA     DATE not null,
  CLASE     VARCHAR2(900),
  PRIORIDAD VARCHAR2(55),
  MENSAJE   VARCHAR2(900)
)
;
alter table DEMO01.APACHELOG4JORACLE
  add constraint PK_IDLOG primary key (ID);

prompt
prompt Creating table USUARIOS
prompt =======================
prompt
create table DEMO01.USUARIOS
(
  IDUSUARIO NUMBER(5) not null,
  NOMBRE    VARCHAR2(30),
  CLAVE     VARCHAR2(10)
)
;
alter table DEMO01.USUARIOS
  add constraint PK_USUARIO primary key (IDUSUARIO);

prompt
prompt Creating table DIRECCION
prompt ========================
prompt
create table DEMO01.DIRECCION
(
  IDDIREC     NUMBER(5) not null,
  DESCRIPCION VARCHAR2(100),
  IDUSUARIO   NUMBER
)
;
alter table DEMO01.DIRECCION
  add constraint PK_DIREC primary key (IDDIREC);
alter table DEMO01.DIRECCION
  add constraint FK_USUARIO foreign key (IDUSUARIO)
  references DEMO01.USUARIOS (IDUSUARIO);

prompt
prompt Creating table EMPLOYEE
prompt =======================
prompt
create table DEMO01.EMPLOYEE
(
  EMPLOYEE_ID   VARCHAR2(8) not null,
  FIRSTNAME     NVARCHAR2(50),
  LASTNAME      NVARCHAR2(50),
  EMAIL_ADDRESS NVARCHAR2(80),
  ADDRESS       NVARCHAR2(80),
  DEPARTMENT    NVARCHAR2(80)
)
;

prompt
prompt Creating table ENTIDAD
prompt ======================
prompt
create table DEMO01.ENTIDAD
(
  KEY   NUMBER(5) not null,
  VALUE VARCHAR2(50) not null
)
;
alter table DEMO01.ENTIDAD
  add primary key (KEY);

prompt
prompt Creating table INDEX_EMPLOYEE
prompt =============================
prompt
create table DEMO01.INDEX_EMPLOYEE
(
  NAME_    VARCHAR2(50 CHAR) not null,
  VALUE_   BLOB,
  SIZE_    NUMBER(10),
  LF_      TIMESTAMP(6),
  DELETED_ NUMBER(1)
)
;
alter table DEMO01.INDEX_EMPLOYEE
  add primary key (NAME_);

prompt
prompt Creating table INDEX_LOG4JDATA
prompt ==============================
prompt
create table DEMO01.INDEX_LOG4JDATA
(
  NAME_    VARCHAR2(50 CHAR) not null,
  VALUE_   BLOB,
  SIZE_    NUMBER(10),
  LF_      TIMESTAMP(6),
  DELETED_ NUMBER(1)
)
;
alter table DEMO01.INDEX_LOG4JDATA
  add primary key (NAME_);

prompt
prompt Creating table INDEX_TBESPEJO
prompt =============================
prompt
create table DEMO01.INDEX_TBESPEJO
(
  NAME_    VARCHAR2(50 CHAR) not null,
  VALUE_   BLOB,
  SIZE_    NUMBER(10),
  LF_      TIMESTAMP(6),
  DELETED_ NUMBER(1)
)
;
alter table DEMO01.INDEX_TBESPEJO
  add primary key (NAME_);

prompt
prompt Creating table LOG4JDATA
prompt ========================
prompt
create table DEMO01.LOG4JDATA
(
  ID              NUMBER not null,
  APP             VARCHAR2(20),
  FECHA           DATE,
  FECHASTRING     VARCHAR2(20),
  CLASE           VARCHAR2(500),
  PRIORIDAD       VARCHAR2(5),
  MENSAJE         VARCHAR2(500),
  INFLOCALIZACION VARCHAR2(500),
  THEAD           VARCHAR2(200),
  THROWABLE       VARCHAR2(3999)
)
;
alter table DEMO01.LOG4JDATA
  add primary key (ID);

prompt
prompt Creating table QRTZ_JOB_DETAILS
prompt ===============================
prompt
create table DEMO01.QRTZ_JOB_DETAILS
(
  JOB_NAME          VARCHAR2(200) not null,
  JOB_GROUP         VARCHAR2(200) not null,
  DESCRIPTION       VARCHAR2(250),
  JOB_CLASS_NAME    VARCHAR2(250) not null,
  IS_DURABLE        VARCHAR2(1) not null,
  IS_VOLATILE       VARCHAR2(1) not null,
  IS_STATEFUL       VARCHAR2(1) not null,
  REQUESTS_RECOVERY VARCHAR2(1) not null,
  JOB_DATA          BLOB
)
;
alter table DEMO01.QRTZ_JOB_DETAILS
  add primary key (JOB_NAME, JOB_GROUP);
create index DEMO01.IDX_QRTZ_J_REQ_RECOVERY on DEMO01.QRTZ_JOB_DETAILS (REQUESTS_RECOVERY);

prompt
prompt Creating table QRTZ_TRIGGERS
prompt ============================
prompt
create table DEMO01.QRTZ_TRIGGERS
(
  TRIGGER_NAME   VARCHAR2(200) not null,
  TRIGGER_GROUP  VARCHAR2(200) not null,
  JOB_NAME       VARCHAR2(200) not null,
  JOB_GROUP      VARCHAR2(200) not null,
  IS_VOLATILE    VARCHAR2(1) not null,
  DESCRIPTION    VARCHAR2(250),
  NEXT_FIRE_TIME NUMBER(13),
  PREV_FIRE_TIME NUMBER(13),
  PRIORITY       NUMBER(13),
  TRIGGER_STATE  VARCHAR2(16) not null,
  TRIGGER_TYPE   VARCHAR2(8) not null,
  START_TIME     NUMBER(13) not null,
  END_TIME       NUMBER(13),
  CALENDAR_NAME  VARCHAR2(200),
  MISFIRE_INSTR  NUMBER(2),
  JOB_DATA       BLOB
)
;
alter table DEMO01.QRTZ_TRIGGERS
  add primary key (TRIGGER_NAME, TRIGGER_GROUP);
alter table DEMO01.QRTZ_TRIGGERS
  add foreign key (JOB_NAME, JOB_GROUP)
  references DEMO01.QRTZ_JOB_DETAILS (JOB_NAME, JOB_GROUP);
create index DEMO01.IDX_QRTZ_T_NEXT_FIRE_TIME on DEMO01.QRTZ_TRIGGERS (NEXT_FIRE_TIME);
create index DEMO01.IDX_QRTZ_T_NFT_ST on DEMO01.QRTZ_TRIGGERS (NEXT_FIRE_TIME, TRIGGER_STATE);
create index DEMO01.IDX_QRTZ_T_STATE on DEMO01.QRTZ_TRIGGERS (TRIGGER_STATE);
create index DEMO01.IDX_QRTZ_T_VOLATILE on DEMO01.QRTZ_TRIGGERS (IS_VOLATILE);

prompt
prompt Creating table QRTZ_BLOB_TRIGGERS
prompt =================================
prompt
create table DEMO01.QRTZ_BLOB_TRIGGERS
(
  TRIGGER_NAME  VARCHAR2(200) not null,
  TRIGGER_GROUP VARCHAR2(200) not null,
  BLOB_DATA     BLOB
)
;
alter table DEMO01.QRTZ_BLOB_TRIGGERS
  add primary key (TRIGGER_NAME, TRIGGER_GROUP);
alter table DEMO01.QRTZ_BLOB_TRIGGERS
  add foreign key (TRIGGER_NAME, TRIGGER_GROUP)
  references DEMO01.QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP);

prompt
prompt Creating table QRTZ_CALENDARS
prompt =============================
prompt
create table DEMO01.QRTZ_CALENDARS
(
  CALENDAR_NAME VARCHAR2(200) not null,
  CALENDAR      BLOB not null
)
;
alter table DEMO01.QRTZ_CALENDARS
  add primary key (CALENDAR_NAME);

prompt
prompt Creating table QRTZ_CRON_TRIGGERS
prompt =================================
prompt
create table DEMO01.QRTZ_CRON_TRIGGERS
(
  TRIGGER_NAME    VARCHAR2(200) not null,
  TRIGGER_GROUP   VARCHAR2(200) not null,
  CRON_EXPRESSION VARCHAR2(120) not null,
  TIME_ZONE_ID    VARCHAR2(80)
)
;
alter table DEMO01.QRTZ_CRON_TRIGGERS
  add primary key (TRIGGER_NAME, TRIGGER_GROUP);
alter table DEMO01.QRTZ_CRON_TRIGGERS
  add foreign key (TRIGGER_NAME, TRIGGER_GROUP)
  references DEMO01.QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP);

prompt
prompt Creating table QRTZ_FIRED_TRIGGERS
prompt ==================================
prompt
create table DEMO01.QRTZ_FIRED_TRIGGERS
(
  ENTRY_ID          VARCHAR2(95) not null,
  TRIGGER_NAME      VARCHAR2(200) not null,
  TRIGGER_GROUP     VARCHAR2(200) not null,
  IS_VOLATILE       VARCHAR2(1) not null,
  INSTANCE_NAME     VARCHAR2(200) not null,
  FIRED_TIME        NUMBER(13) not null,
  PRIORITY          NUMBER(13) not null,
  STATE             VARCHAR2(16) not null,
  JOB_NAME          VARCHAR2(200),
  JOB_GROUP         VARCHAR2(200),
  IS_STATEFUL       VARCHAR2(1),
  REQUESTS_RECOVERY VARCHAR2(1)
)
;
alter table DEMO01.QRTZ_FIRED_TRIGGERS
  add primary key (ENTRY_ID);
create index DEMO01.IDX_QRTZ_FT_JOB_GROUP on DEMO01.QRTZ_FIRED_TRIGGERS (JOB_GROUP);
create index DEMO01.IDX_QRTZ_FT_JOB_NAME on DEMO01.QRTZ_FIRED_TRIGGERS (JOB_NAME);
create index DEMO01.IDX_QRTZ_FT_JOB_REQ_RECOVERY on DEMO01.QRTZ_FIRED_TRIGGERS (REQUESTS_RECOVERY);
create index DEMO01.IDX_QRTZ_FT_JOB_STATEFUL on DEMO01.QRTZ_FIRED_TRIGGERS (IS_STATEFUL);
create index DEMO01.IDX_QRTZ_FT_TRIG_GROUP on DEMO01.QRTZ_FIRED_TRIGGERS (TRIGGER_GROUP);
create index DEMO01.IDX_QRTZ_FT_TRIG_INST_NAME on DEMO01.QRTZ_FIRED_TRIGGERS (INSTANCE_NAME);
create index DEMO01.IDX_QRTZ_FT_TRIG_NAME on DEMO01.QRTZ_FIRED_TRIGGERS (TRIGGER_NAME);
create index DEMO01.IDX_QRTZ_FT_TRIG_NM_GP on DEMO01.QRTZ_FIRED_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP);
create index DEMO01.IDX_QRTZ_FT_TRIG_VOLATILE on DEMO01.QRTZ_FIRED_TRIGGERS (IS_VOLATILE);

prompt
prompt Creating table QRTZ_HISTORIAL
prompt =============================
prompt
create table DEMO01.QRTZ_HISTORIAL
(
  IDHISTORIAL    NUMBER(8) not null,
  IDTRIGGERNAME  VARCHAR2(100),
  IDTRIGGERGROUP VARCHAR2(100),
  IDJOBNAME      VARCHAR2(100),
  IDJOBGROUP     VARCHAR2(100),
  FECHAINICIO    DATE,
  FECHAFIN       DATE,
  ESTADO         VARCHAR2(100)
)
;
alter table DEMO01.QRTZ_HISTORIAL
  add primary key (IDHISTORIAL);

prompt
prompt Creating table QRTZ_JOB_LISTENERS
prompt =================================
prompt
create table DEMO01.QRTZ_JOB_LISTENERS
(
  JOB_NAME     VARCHAR2(200) not null,
  JOB_GROUP    VARCHAR2(200) not null,
  JOB_LISTENER VARCHAR2(200) not null
)
;
alter table DEMO01.QRTZ_JOB_LISTENERS
  add primary key (JOB_NAME, JOB_GROUP, JOB_LISTENER);
alter table DEMO01.QRTZ_JOB_LISTENERS
  add foreign key (JOB_NAME, JOB_GROUP)
  references DEMO01.QRTZ_JOB_DETAILS (JOB_NAME, JOB_GROUP);

prompt
prompt Creating table QRTZ_LOCKS
prompt =========================
prompt
create table DEMO01.QRTZ_LOCKS
(
  LOCK_NAME VARCHAR2(40) not null
)
;
alter table DEMO01.QRTZ_LOCKS
  add primary key (LOCK_NAME);

prompt
prompt Creating table QRTZ_PAUSED_TRIGGER_GRPS
prompt =======================================
prompt
create table DEMO01.QRTZ_PAUSED_TRIGGER_GRPS
(
  TRIGGER_GROUP VARCHAR2(200) not null
)
;
alter table DEMO01.QRTZ_PAUSED_TRIGGER_GRPS
  add primary key (TRIGGER_GROUP);

prompt
prompt Creating table QRTZ_SCHEDULER_STATE
prompt ===================================
prompt
create table DEMO01.QRTZ_SCHEDULER_STATE
(
  INSTANCE_NAME     VARCHAR2(200) not null,
  LAST_CHECKIN_TIME NUMBER(13) not null,
  CHECKIN_INTERVAL  NUMBER(13) not null
)
;
alter table DEMO01.QRTZ_SCHEDULER_STATE
  add primary key (INSTANCE_NAME);

prompt
prompt Creating table QRTZ_SIMPLE_TRIGGERS
prompt ===================================
prompt
create table DEMO01.QRTZ_SIMPLE_TRIGGERS
(
  TRIGGER_NAME    VARCHAR2(200) not null,
  TRIGGER_GROUP   VARCHAR2(200) not null,
  REPEAT_COUNT    NUMBER(7) not null,
  REPEAT_INTERVAL NUMBER(12) not null,
  TIMES_TRIGGERED NUMBER(7) not null
)
;
alter table DEMO01.QRTZ_SIMPLE_TRIGGERS
  add primary key (TRIGGER_NAME, TRIGGER_GROUP);
alter table DEMO01.QRTZ_SIMPLE_TRIGGERS
  add foreign key (TRIGGER_NAME, TRIGGER_GROUP)
  references DEMO01.QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP);

prompt
prompt Creating table QRTZ_TRIGGER_LISTENERS
prompt =====================================
prompt
create table DEMO01.QRTZ_TRIGGER_LISTENERS
(
  TRIGGER_NAME     VARCHAR2(200) not null,
  TRIGGER_GROUP    VARCHAR2(200) not null,
  TRIGGER_LISTENER VARCHAR2(200) not null
)
;
alter table DEMO01.QRTZ_TRIGGER_LISTENERS
  add primary key (TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_LISTENER);
alter table DEMO01.QRTZ_TRIGGER_LISTENERS
  add foreign key (TRIGGER_NAME, TRIGGER_GROUP)
  references DEMO01.QRTZ_TRIGGERS (TRIGGER_NAME, TRIGGER_GROUP);

prompt
prompt Creating table TB_ESPEJO
prompt ========================
prompt
create table DEMO01.TB_ESPEJO
(
  ID_ESPEJO NUMBER not null,
  ID        VARCHAR2(20),
  DATATODA  LONG,
  FECHA     DATE,
  INSTANCIA VARCHAR2(50)
)
;
alter table DEMO01.TB_ESPEJO
  add primary key (ID_ESPEJO);

prompt
prompt Creating table TB_USER
prompt ======================
prompt
create table DEMO01.TB_USER
(
  CODE      INTEGER not null,
  NAME      VARCHAR2(45),
  PASSWORD  VARCHAR2(45),
  GENDER    VARCHAR2(45),
  COUNTRY   VARCHAR2(45),
  ABOUTYOU  VARCHAR2(45),
  COMMUNITY VARCHAR2(100)
)
;
alter table DEMO01.TB_USER
  add primary key (CODE);

prompt
prompt Creating table TRADE
prompt ====================
prompt
create table DEMO01.TRADE
(
  TRADE_ID NUMBER(5) not null,
  SIDE     VARCHAR2(10) not null,
  SYMBOL   VARCHAR2(6) not null,
  SHARES   NUMBER(4,2) not null,
  PRICE    NUMBER(4,2) not null,
  STATE    VARCHAR2(20) not null
)
;
alter table DEMO01.TRADE
  add primary key (TRADE_ID);

prompt
prompt Creating sequence SEC_ESPEJO
prompt ============================
prompt
create sequence DEMO01.SEC_ESPEJO
minvalue 1
maxvalue 99999999999
start with 182
increment by 1
cache 20;

prompt
prompt Creating sequence SEC_LOGGER
prompt ============================
prompt
create sequence DEMO01.SEC_LOGGER
minvalue 1
maxvalue 99999999999
start with 3100
increment by 1
cache 20;

prompt
prompt Creating sequence SEC_LOG4JDATA
prompt ===============================
prompt
create sequence DEMO01.SEC_LOG4JDATA
minvalue 1
maxvalue 99999999999
start with 37141
increment by 1
cache 20;

prompt
prompt Creating sequence SEC_QRTZ_HISTORIAL
prompt ====================================
prompt
create sequence DEMO01.SEC_QRTZ_HISTORIAL
minvalue 1
maxvalue 99999999999
start with 1
increment by 1
cache 20;

prompt
prompt Creating package PQ_DEMO01
prompt ==========================
prompt
CREATE OR REPLACE PACKAGE DEMO01.PQ_DEMO01 is
 procedure p_obt_all_entidades(    RC_SALIDA            OUT SYS_REFCURSOR,
                                    an_id_key            in demo01.entidad.key%type,
                                    an_id_value          in demo01.entidad.value%type
                                   );
     end;
/

prompt
prompt Creating package body PQ_DEMO01
prompt ===============================
prompt
CREATE OR REPLACE PACKAGE BODY DEMO01.PQ_DEMO01 is

   procedure p_obt_all_entidades(   RC_SALIDA            OUT SYS_REFCURSOR,
                                    an_id_key            in demo01.entidad.key%type,
                                    an_id_value          in demo01.entidad.value%type
                                   )
    is

     begin
       OPEN RC_SALIDA FOR
         select e.*
         from  entidad e
         where e.key = an_id_key  and e.value = an_id_value;
       
       end;


   end;
/

prompt
prompt Creating trigger T_ESPEJO_BU
prompt ============================
prompt
create or replace trigger DEMO01.T_ESPEJO_BU
  before update on DEMO01.TB_ESPEJO
  for each row

declare


begin
  :new.fecha := sysdate;

end T_ESPEJO_BU;
/

prompt
prompt Creating trigger T_ESPEJO_I
prompt ===========================
prompt
create or replace trigger DEMO01.T_ESPEJO_I
  before insert on DEMO01.TB_ESPEJO
  for each row
declare

begin

  IF :new.ID is null then

     SELECT SEC_ESPEJO.nextval
     into :new.ID_ESPEJO
     from dual;

  end if;

end T_ESPEJO_I;
/

prompt
prompt Creating trigger T_LOG_I
prompt ========================
prompt
create or replace trigger DEMO01.T_LOG_I
  before insert on DEMO01.APACHELOG4JORACLE
  for each row
declare

begin

  IF :new.ID is null then

     SELECT SEC_LOGGER.nextval
     into :new.ID
     from dual;

  end if;

end T_LOG_I;
/

prompt
prompt Creating trigger T_LOG4JDATA_I
prompt ==============================
prompt
create or replace trigger DEMO01.T_LOG4JDATA_I
  before insert on DEMO01.LOG4JDATA
  for each row
declare

begin

  IF :new.ID is null then

     SELECT SEC_LOG4JDATA.nextval
     into :new.ID
     from dual;

  end if;

end T_LOG4JDATA_I;
/

prompt
prompt Creating trigger T_QRTZ_HISTORIAL_I
prompt ===================================
prompt
create or replace trigger DEMO01.T_QRTZ_HISTORIAL_I
  before insert on DEMO01.QRTZ_HISTORIAL
  for each row
declare

begin

  IF :new.IDHISTORIAL is null then

     SELECT SEC_QRTZ_HISTORIAL.nextval
     into :new.IDHISTORIAL
     from dual;

  end if;

end T_QRTZ_HISTORIAL_I;
/


spool off
